-- Migrationscripts for ebean unittest
-- drop dependencies
delimiter $$
begin
if exists (select constname from syscat.tabconst where tabschema = current_schema and ucase(constname) = 'FK_MIGTEST_FK_CASCADE_ONE_ID' and ucase(tabname) = 'MIGTEST_FK_CASCADE') then
  prepare stmt from 'alter table migtest_fk_cascade drop constraint fk_migtest_fk_cascade_one_id';
  execute stmt;
end if;
end$$;
delimiter $$
begin
if exists (select constname from syscat.tabconst where tabschema = current_schema and ucase(constname) = 'FK_MIGTEST_FK_SET_NULL_ONE_ID' and ucase(tabname) = 'MIGTEST_FK_SET_NULL') then
  prepare stmt from 'alter table migtest_fk_set_null drop constraint fk_migtest_fk_set_null_one_id';
  execute stmt;
end if;
end$$;
delimiter $$
begin
if exists (select constname from syscat.tabconst where tabschema = current_schema and ucase(constname) = 'CK_MIGTEST_E_BASIC_STATUS' and ucase(tabname) = 'MIGTEST_E_BASIC') then
  prepare stmt from 'alter table migtest_e_basic drop constraint ck_migtest_e_basic_status';
  execute stmt;
end if;
end$$;
delimiter $$
begin
if exists (select constname from syscat.tabconst where tabschema = current_schema and ucase(constname) = 'CK_MIGTEST_E_BASIC_STATUS2' and ucase(tabname) = 'MIGTEST_E_BASIC') then
  prepare stmt from 'alter table migtest_e_basic drop constraint ck_migtest_e_basic_status2';
  execute stmt;
end if;
end$$;
delimiter $$
begin
if exists (select constname from syscat.tabconst where tabschema = current_schema and ucase(constname) = 'UQ_MIGTEST_E_BASIC_INDEXTEST2' and ucase(tabname) = 'MIGTEST_E_BASIC') then
  prepare stmt from 'alter table migtest_e_basic drop constraint uq_migtest_e_basic_indextest2';
  execute stmt;
end if;
end$$
delimiter $$
begin
if exists (select indname from syscat.indexes where indschema = current_schema and ucase(indname) = 'UQ_MIGTEST_E_BASIC_INDEXTEST2') then
  prepare stmt from 'drop index uq_migtest_e_basic_indextest2';
  execute stmt;
end if;
end$$;
delimiter $$
begin
if exists (select constname from syscat.tabconst where tabschema = current_schema and ucase(constname) = 'UQ_MIGTEST_E_BASIC_INDEXTEST6' and ucase(tabname) = 'MIGTEST_E_BASIC') then
  prepare stmt from 'alter table migtest_e_basic drop constraint uq_migtest_e_basic_indextest6';
  execute stmt;
end if;
end$$
delimiter $$
begin
if exists (select indname from syscat.indexes where indschema = current_schema and ucase(indname) = 'UQ_MIGTEST_E_BASIC_INDEXTEST6') then
  prepare stmt from 'drop index uq_migtest_e_basic_indextest6';
  execute stmt;
end if;
end$$;
delimiter $$
begin
if exists (select constname from syscat.tabconst where tabschema = current_schema and ucase(constname) = 'CK_MIGTEST_E_ENUM_TEST_STATUS' and ucase(tabname) = 'MIGTEST_E_ENUM') then
  prepare stmt from 'alter table migtest_e_enum drop constraint ck_migtest_e_enum_test_status';
  execute stmt;
end if;
end$$;
delimiter $$
begin
if exists (select indname from syscat.indexes where indschema = current_schema and ucase(indname) = 'IX_MIGTEST_E_BASIC_INDEXTEST1') then
  prepare stmt from 'drop index ix_migtest_e_basic_indextest1';
  execute stmt;
end if;
end$$;
delimiter $$
begin
if exists (select indname from syscat.indexes where indschema = current_schema and ucase(indname) = 'IX_MIGTEST_E_BASIC_INDEXTEST5') then
  prepare stmt from 'drop index ix_migtest_e_basic_indextest5';
  execute stmt;
end if;
end$$;
delimiter $$
begin
if exists (select indname from syscat.indexes where indschema = current_schema and ucase(indname) = 'IX_MIGTEST_QUOTED_STATUS1') then
  prepare stmt from 'drop index ix_migtest_quoted_status1';
  execute stmt;
end if;
end$$;
-- apply changes
create table drop_main (
  id                            integer generated by default as identity not null,
  constraint pk_drop_main primary key (id)
);

create table drop_main_drop_ref_many (
  drop_main_id                  integer not null,
  drop_ref_many_id              integer not null,
  constraint pk_drop_main_drop_ref_many primary key (drop_main_id,drop_ref_many_id)
);

create table drop_ref_many (
  id                            integer generated by default as identity not null,
  constraint pk_drop_ref_many primary key (id)
);

create table drop_ref_one (
  id                            integer generated by default as identity not null,
  parent_id                     integer,
  constraint pk_drop_ref_one primary key (id)
);

create table drop_ref_one_to_one (
  id                            integer generated by default as identity not null,
  parent_id                     integer,
  constraint pk_drop_ref_one_to_one primary key (id)
);

create table migtest_e_test_binary (
  id                            integer generated by default as identity not null,
  test_byte16                   varbinary(16),
  test_byte256                  varbinary(256),
  test_byte512                  varbinary(512),
  test_byte1k                   varbinary(1024),
  test_byte2k                   varbinary(2048),
  test_byte4k                   varbinary(4096),
  test_byte8k                   blob(8192),
  test_byte16k                  blob(16384),
  test_byte32k                  blob(32768),
  test_byte64k                  blob(65536),
  test_byte128k                 blob(131072),
  test_byte256k                 blob(262144),
  test_byte512k                 blob(524288),
  test_byte1m                   blob(1048576),
  test_byte2m                   blob(2097152),
  test_byte4m                   blob(4194304),
  test_byte8m                   blob(8388608),
  test_byte16m                  blob(16777216),
  test_byte32m                  blob(33554432),
  constraint pk_migtest_e_test_binary primary key (id)
);

create table migtest_e_test_json (
  id                            integer generated by default as identity not null,
  json255                       varchar(255),
  json256                       varchar(256),
  json512                       varchar(512),
  json1k                        varchar(1024),
  json2k                        varchar(2048),
  json4k                        clob(4096),
  json8k                        clob(8192),
  json16k                       clob(16384),
  json32k                       clob(32768),
  json64k                       clob(65536),
  json128k                      clob(131072),
  json256k                      clob(262144),
  json512k                      clob(524288),
  json1m                        clob(1048576),
  json2m                        clob(2097152),
  json4m                        clob(4194304),
  json8m                        clob(8388608),
  json16m                       clob(16777216),
  json32m                       clob(33554432),
  constraint pk_migtest_e_test_json primary key (id)
);

create table migtest_e_test_lob (
  id                            integer generated by default as identity not null,
  lob255                        clob,
  lob256                        clob,
  lob512                        clob,
  lob1k                         clob,
  lob2k                         clob,
  lob4k                         clob,
  lob8k                         clob,
  lob16k                        clob,
  lob32k                        clob,
  lob64k                        clob,
  lob128k                       clob,
  lob256k                       clob,
  lob512k                       clob,
  lob1m                         clob,
  lob2m                         clob,
  lob4m                         clob,
  lob8m                         clob,
  lob16m                        clob,
  lob32m                        clob,
  constraint pk_migtest_e_test_lob primary key (id)
);

create table migtest_e_test_varchar (
  id                            integer generated by default as identity not null,
  varchar255                    varchar(255),
  varchar256                    varchar(256),
  varchar512                    varchar(512),
  varchar1k                     varchar(1024),
  varchar2k                     varchar(2048),
  varchar4k                     clob(4096),
  varchar8k                     clob(8192),
  varchar16k                    clob(16384),
  varchar32k                    clob(32768),
  varchar64k                    clob(65536),
  varchar128k                   clob(131072),
  varchar256k                   clob(262144),
  varchar512k                   clob(524288),
  varchar1m                     clob(1048576),
  varchar2m                     clob(2097152),
  varchar4m                     clob(4194304),
  varchar8m                     clob(8388608),
  varchar16m                    clob(16777216),
  varchar32m                    clob(33554432),
  constraint pk_migtest_e_test_varchar primary key (id)
);

create table migtest_e_user (
  id                            integer generated by default as identity not null,
  constraint pk_migtest_e_user primary key (id)
);

create table migtest_mtm_c_migtest_mtm_m (
  migtest_mtm_c_id              integer not null,
  migtest_mtm_m_id              bigint not null,
  constraint pk_migtest_mtm_c_migtest_mtm_m primary key (migtest_mtm_c_id,migtest_mtm_m_id)
) in TESTTS index in TESTTS long in TESTTS;

create table migtest_mtm_m_migtest_mtm_c (
  migtest_mtm_m_id              bigint not null,
  migtest_mtm_c_id              integer not null,
  constraint pk_migtest_mtm_m_migtest_mtm_c primary key (migtest_mtm_m_id,migtest_mtm_c_id)
) in TSMASTER index in TSMASTER long in TSMASTER;

create table migtest_mtm_m_phone_numbers (
  migtest_mtm_m_id              bigint not null,
  value                         varchar(255) not null
) in TSMASTER index in TSMASTER long in TSMASTER;


update migtest_e_basic set status = 'A' where status is null;

-- db2 does not support parial null indices :( - so we have to clean;
update migtest_e_basic set status = 'N' where id = 1;

insert into migtest_e_user (id) select distinct user_id from migtest_e_basic;
CALL SYSPROC.ADMIN_MOVE_TABLE(CURRENT_SCHEMA,'MIGTEST_E_BASIC','USERSPACE1','USERSPACE1','USERSPACE1','','','','','','MOVE');

-- NOTE: table has @History - special migration may be necessary
update migtest_e_history2 set test_string = 'unknown' where test_string is null;
alter table migtest_e_history2 drop versioning;
alter table migtest_e_history3 drop versioning;
alter table migtest_e_history4 drop versioning;
alter table migtest_e_history5 drop versioning;

-- NOTE: table has @History - special migration may be necessary
update migtest_e_history6 set test_number1 = 42 where test_number1 is null;
alter table migtest_e_history6 drop versioning;
alter table "table" drop versioning;
CALL SYSPROC.ADMIN_MOVE_TABLE(CURRENT_SCHEMA,'MIGTEST_MTM_C','TESTTS','TESTTS','TESTTS','','','','','','MOVE');
CALL SYSPROC.ADMIN_MOVE_TABLE(CURRENT_SCHEMA,'MIGTEST_MTM_M','TSMASTER','TSMASTER','TSMASTER','','','','','','MOVE');
-- apply alter tables
alter table "table" alter column textfield drop not null;
alter table "table" add column "select" varchar(255);
alter table "table" add column textfield2 varchar(255);
call sysproc.admin_cmd('reorg table "table" ${reorgArgs}');
alter table migtest_ckey_detail add column one_key integer;
alter table migtest_ckey_detail add column two_key varchar(127);
alter table migtest_ckey_parent add column assoc_id integer;
alter table migtest_e_basic alter column status set default 'A';
alter table migtest_e_basic alter column status set not null;
alter table migtest_e_basic alter column status2 set data type varchar(127);
alter table migtest_e_basic alter column status2 drop default;
alter table migtest_e_basic alter column status2 drop not null;
alter table migtest_e_basic alter column a_lob drop default;
alter table migtest_e_basic alter column a_lob drop not null;
alter table migtest_e_basic alter column user_id drop not null;
alter table migtest_e_basic add column new_string_field varchar(255) default 'foo''bar' not null;
alter table migtest_e_basic add column new_boolean_field boolean default true not null;
alter table migtest_e_basic add column new_boolean_field2 boolean default true not null;
alter table migtest_e_basic add column progress integer default 0 not null;
alter table migtest_e_basic add column new_integer integer default 42 not null;
call sysproc.admin_cmd('reorg table migtest_e_basic ${reorgArgs}');
alter table migtest_e_history add column sys_period_start timestamp(12) not null generated always as row begin;
alter table migtest_e_history add column sys_period_end timestamp(12) not null generated always as row end;
alter table migtest_e_history add column sys_period_txn timestamp(12) generated always as transaction start id;
alter table migtest_e_history add period system_time (sys_period_start,sys_period_end);
alter table migtest_e_history alter column test_string set data type bigint;
call sysproc.admin_cmd('reorg table migtest_e_history ${reorgArgs}');
alter table migtest_e_history2 alter column test_string set default 'unknown';
alter table migtest_e_history2 alter column test_string set not null;
alter table migtest_e_history2 add column test_string2 varchar(255);
alter table migtest_e_history2 add column test_string3 varchar(255) default 'unknown' not null;
alter table migtest_e_history2 add column new_column varchar(20);
call sysproc.admin_cmd('reorg table migtest_e_history2 ${reorgArgs}');
alter table migtest_e_history2_history alter column test_string set not null;
alter table migtest_e_history2_history add column test_string2 varchar(255);
alter table migtest_e_history2_history add column test_string3 varchar(255) default 'unknown' not null;
alter table migtest_e_history2_history add column new_column varchar(20);
call sysproc.admin_cmd('reorg table migtest_e_history2_history ${reorgArgs}');
alter table migtest_e_history4 alter column test_number set data type bigint;
call sysproc.admin_cmd('reorg table migtest_e_history4 ${reorgArgs}');
alter table migtest_e_history4_history alter column test_number set data type bigint;
call sysproc.admin_cmd('reorg table migtest_e_history4_history ${reorgArgs}');
alter table migtest_e_history5 add column test_boolean boolean default false not null;
alter table migtest_e_history5_history add column test_boolean boolean default false not null;
alter table migtest_e_history6 alter column test_number1 set default 42;
alter table migtest_e_history6 alter column test_number1 set not null;
alter table migtest_e_history6 alter column test_number2 drop not null;
call sysproc.admin_cmd('reorg table migtest_e_history6 ${reorgArgs}');
alter table migtest_e_history6_history alter column test_number1 set not null;
alter table migtest_e_history6_history alter column test_number2 drop not null;
call sysproc.admin_cmd('reorg table migtest_e_history6_history ${reorgArgs}');
alter table migtest_e_softdelete add column deleted boolean default false not null;
alter table migtest_oto_child add column master_id bigint;
alter table table_history alter column textfield drop not null;
alter table table_history add column "select" varchar(255);
alter table table_history add column textfield2 varchar(255);
call sysproc.admin_cmd('reorg table table_history ${reorgArgs}');
-- apply post alter
create unique index uq_drop_ref_one_to_one_parent_id on drop_ref_one_to_one(parent_id) exclude null keys;
alter table migtest_e_basic add constraint ck_migtest_e_basic_status check ( status in ('N','A','I','?'));
create unique index uq_migtest_e_basic_description on migtest_e_basic(description) exclude null keys;
update migtest_e_basic set new_boolean_field = old_boolean;

alter table migtest_e_basic add constraint ck_migtest_e_basic_progress check ( progress in (0,1,2));
create unique index uq_migtest_e_basic_status_indextest1 on migtest_e_basic(status,indextest1) exclude null keys;
create unique index uq_migtest_e_basic_name on migtest_e_basic(name) exclude null keys;
create unique index uq_migtest_e_basic_indextest4 on migtest_e_basic(indextest4) exclude null keys;
create unique index uq_migtest_e_basic_indextest5 on migtest_e_basic(indextest5) exclude null keys;
create table migtest_e_history_history as (select * from migtest_e_history) with no data in MAIN index in MAIN long in MAIN;
alter table migtest_e_history add versioning use history table migtest_e_history_history;
comment on column migtest_e_history.test_string is 'Column altered to long now';
comment on table migtest_e_history is 'We have history now';
alter table migtest_e_history2 add versioning use history table migtest_e_history2_history;
alter table migtest_e_history3 add versioning use history table migtest_e_history3_history;
alter table migtest_e_history4 add versioning use history table migtest_e_history4_history;
alter table migtest_e_history5 add versioning use history table migtest_e_history5_history;
alter table migtest_e_history6 add versioning use history table migtest_e_history6_history;
comment on column "table"."index" is 'this is an other comment';
alter table "table" add versioning use history table table_history;
create unique index uq_table_select on "table"("select") exclude null keys;
-- foreign keys and indices
create index ix_drop_main_drop_ref_many_drop_main on drop_main_drop_ref_many (drop_main_id);
alter table drop_main_drop_ref_many add constraint fk_drop_main_drop_ref_many_drop_main foreign key (drop_main_id) references drop_main (id) on delete restrict on update restrict;

create index ix_drop_main_drop_ref_many_drop_ref_many on drop_main_drop_ref_many (drop_ref_many_id);
alter table drop_main_drop_ref_many add constraint fk_drop_main_drop_ref_many_drop_ref_many foreign key (drop_ref_many_id) references drop_ref_many (id) on delete restrict on update restrict;

create index ix_drop_ref_one_parent_id on drop_ref_one (parent_id);
alter table drop_ref_one add constraint fk_drop_ref_one_parent_id foreign key (parent_id) references drop_main (id) on delete restrict on update restrict;

alter table drop_ref_one_to_one add constraint fk_drop_ref_one_to_one_parent_id foreign key (parent_id) references drop_main (id) on delete restrict on update restrict;

create index ix_migtest_mtm_c_migtest_mtm_m_migtest_mtm_c on migtest_mtm_c_migtest_mtm_m (migtest_mtm_c_id);
alter table migtest_mtm_c_migtest_mtm_m add constraint fk_migtest_mtm_c_migtest_mtm_m_migtest_mtm_c foreign key (migtest_mtm_c_id) references migtest_mtm_c (id) on delete restrict on update restrict;

create index ix_migtest_mtm_c_migtest_mtm_m_migtest_mtm_m on migtest_mtm_c_migtest_mtm_m (migtest_mtm_m_id);
alter table migtest_mtm_c_migtest_mtm_m add constraint fk_migtest_mtm_c_migtest_mtm_m_migtest_mtm_m foreign key (migtest_mtm_m_id) references migtest_mtm_m (id) on delete restrict on update restrict;

create index ix_migtest_mtm_m_migtest_mtm_c_migtest_mtm_m on migtest_mtm_m_migtest_mtm_c (migtest_mtm_m_id);
alter table migtest_mtm_m_migtest_mtm_c add constraint fk_migtest_mtm_m_migtest_mtm_c_migtest_mtm_m foreign key (migtest_mtm_m_id) references migtest_mtm_m (id) on delete restrict on update restrict;

create index ix_migtest_mtm_m_migtest_mtm_c_migtest_mtm_c on migtest_mtm_m_migtest_mtm_c (migtest_mtm_c_id);
alter table migtest_mtm_m_migtest_mtm_c add constraint fk_migtest_mtm_m_migtest_mtm_c_migtest_mtm_c foreign key (migtest_mtm_c_id) references migtest_mtm_c (id) on delete restrict on update restrict;

create index ix_migtest_mtm_m_phone_numbers_migtest_mtm_m_id on migtest_mtm_m_phone_numbers (migtest_mtm_m_id);
alter table migtest_mtm_m_phone_numbers add constraint fk_migtest_mtm_m_phone_numbers_migtest_mtm_m_id foreign key (migtest_mtm_m_id) references migtest_mtm_m (id) on delete restrict on update restrict;

alter table migtest_ckey_detail add constraint fk_migtest_ckey_detail_parent foreign key (one_key,two_key) references migtest_ckey_parent (one_key,two_key) on delete restrict on update restrict;
create index ix_migtest_ckey_parent_assoc_id on migtest_ckey_parent (assoc_id);
alter table migtest_ckey_parent add constraint fk_migtest_ckey_parent_assoc_id foreign key (assoc_id) references migtest_ckey_assoc (id) on delete restrict on update restrict;

alter table migtest_fk_cascade add constraint fk_migtest_fk_cascade_one_id foreign key (one_id) references migtest_fk_cascade_one (id) on delete restrict on update restrict;
alter table migtest_fk_none add constraint fk_migtest_fk_none_one_id foreign key (one_id) references migtest_fk_one (id) on delete restrict on update restrict;
alter table migtest_fk_none_via_join add constraint fk_migtest_fk_none_via_join_one_id foreign key (one_id) references migtest_fk_one (id) on delete restrict on update restrict;
alter table migtest_fk_set_null add constraint fk_migtest_fk_set_null_one_id foreign key (one_id) references migtest_fk_one (id) on delete restrict on update restrict;
alter table migtest_e_basic add constraint fk_migtest_e_basic_user_id foreign key (user_id) references migtest_e_user (id) on delete restrict on update restrict;
alter table migtest_oto_child add constraint fk_migtest_oto_child_master_id foreign key (master_id) references migtest_oto_master (id) on delete restrict on update restrict;

create index ix_migtest_e_basic_indextest3 on migtest_e_basic (indextest3);
create index ix_migtest_e_basic_indextest6 on migtest_e_basic (indextest6);
create index ix_table_textfield2 on "table" (textfield2);
